﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace DAO
{
    public class DataProvider
    {
        #region

        SqlConnection connect;
        string sqlConnectionstring;
        public string _error;

        #endregion

        public DataProvider()
        {
            sqlConnectionstring = ConfigurationManager.ConnectionStrings["Nhom16pttkpmConnectionString"].ConnectionString;
            connect = new SqlConnection(sqlConnectionstring);

        }

        public bool OpenConnect()// ham mo ket noi
        {
            try
            {
                // kiem tra xem da connect chua, neu chua thi khoi tao no
                if (connect == null)
                {
                    connect = new SqlConnection(sqlConnectionstring);
                }
                if (connect.State == ConnectionState.Open)//neu trang thai la dang mo
                {
                    connect.Close();// dong no

                }
                connect.Open();
                return true;

            }
            catch (SqlException ex)
            {
                _error = ex.Message;
                return false;
            }


        }
        public bool CloseConnect()// Ham dong ket noi
        {
            if (connect != null)
            {
                if (connect.State == ConnectionState.Open)
                {
                    connect.Close();
                }
                return true;

            }
            return false;

        }

        public bool ExecutenonQuery(string StoreName, SqlParameter[] sqlparam)// thuc thi cau truy van co thay doi du lieu 
        {
            try
            {
                if (OpenConnect() == true)
                {
                    SqlCommand cmd = connect.CreateCommand();// doi tuong nhan cau sql
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = StoreName;
                    if (sqlparam != null)
                    {
                        cmd.Parameters.AddRange(sqlparam);
                    }
                    cmd.ExecuteNonQuery();
                    _error = string.Empty;
                }
                return true;

            }
            catch (SqlException ex)
            {
                _error = ex.Message;
                return false;                
            }
            finally
            {
                CloseConnect();
            }

        }

        public bool ExecutenonQuery(string StoreName)// thuc thi cau truy van co thay doi du lieu 
        {
            try
            {
                if (OpenConnect() == true)
                {
                    SqlCommand cmd = connect.CreateCommand();// doi tuong nhan cau sql
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = StoreName;
                    cmd.ExecuteNonQuery();
                    _error = string.Empty;
                }
                return true;

            }
            catch (SqlException ex)
            {
                _error = ex.Message;
                return false;

            }
            finally
            {
                CloseConnect();
            }

        }


        public DataTable ExecuteQuery(string StoreName, SqlParameter[] sqlparam) // Thuc thi cau truy van khong lam thay doi du lieu
        {
            DataTable dtb = null;
            try
            {
                if (OpenConnect() == true)
                {
                    SqlCommand cmd = connect.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = StoreName;
                    if (sqlparam != null)
                    {
                        cmd.Parameters.AddRange(sqlparam);
                    }
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    dtb = new DataTable();
                    da.Fill(dtb);
                    _error = "";
                }
            }
            catch (SqlException ex)
            {
                _error = ex.Message;

            }
            finally
            {
                CloseConnect();
            }
            return dtb;

        }

        public DataTable ExecuteQuery(string StoreName) // Thuc thi cau truy van khong lam thay doi du lieu, khong co tham so dau vao
        {
            DataTable dtb = null;
            try
            {
                if (OpenConnect() == true)
                {
                    SqlCommand cmd = connect.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = StoreName;
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    dtb = new DataTable();
                    da.Fill(dtb);
                    _error = "";
                }
            }
            catch (SqlException ex)
            {
                _error = ex.Message;

            }
            finally
            {
                CloseConnect();
            }
            return dtb;

        }
    }
}
